const express = require('express');
const router = express.Router();
const db = require('../../../../util/dbConfig/dbConfig')

//查询
router.post('/',async (req,res) => {
    //从session中获取用户信息
    let userInfo = req.session.userInfo
    let {
        //下面三个参数 只可有一个
        userName,   //牧户姓名
        eweId,      //母羊编号
        ramId,      //公羊编号
        sortLowNum,   //第几列的排序数，从左往右，从1开始
        ISAsc,         //增还是减  1为增，0为减
        page,      //页码
        isPage,     //是否分页 1为分页0为不分页
    } = req.body

    if(!page){
        return res.send({
            code: 1,
            msg: '请输入页码'
        })
    }

    let newPage = (page - 1) * 10 //跳过多页

    let arr = [
        userName,   //牧户姓名
        eweId,      //母羊编号
        ramId,      //公羊编号
  ]
  console.log(arr)
    let j = 0
    for(let i =0;i<arr.length;i++){
        if(arr[i] === null || arr[i] === undefined || arr[i] === ''){
            j++
        }
    }
    if(j !== 2){
        console.log(j);
        return res.send({
            code: 1,
            msg: '参数错误'
        })
    }
    try{
        let sql = `select
            a.ID, 
            c.NAME,
            a.EWE_ID,
            a.RAM_ID,
            a.BEGIN_DATE,
            a.FINISH_DATE,
            a.PMSG,
            a.PG,
            a.ADOPT_PRECISE,
            a.VITALITY,
            a.DENSITY,
            a.DILUTION_RATIO,
            a.DATE,
            a.HEAT_FIRM1_DATE,
            a.HEAT_FIRM1_NUMBER,
            a.HEAT_FIRM2_DATE,
            a.HEAT_FIRM2_NUMBER,
            a.SPERM_RECORD1,
            a.SPERM_RECORD1_DATE,
            a.SPERM_RECORD2,
            a.SPERM_RECORD2_DATE,
            a.remark
            from hybridization a , sheep b , user c
            where a.EWE_ID = b.ID and c.ID = b.USER_ID
            `
        //判断是不是管理员
        if(userInfo.admin === 0){
            //普通用户
            sql = sql + `and b.USER_ID=${userInfo._id}`
            userName = undefined
        }
        if(userName){
            let userSql = `select ID from user where NAME='${userName}'`
          let userFindResult = await db.SySqlConnect(userSql, [])
          if (userFindResult.length === 0) {
            return res.send({
              code: 2,
              msg: `无${userName}用户`
            })
          }
            let userId = userFindResult[0]['ID']
            sql = sql + `and b.USER_ID =${userId}`
        }else if(eweId){
            sql = sql + ' ' +  `and b.ID=${eweId} and b.SEX=2`
        }else if(ramId){
            sql = sql + ' ' + `and b.ID=${ramId} and b.SEX=1`
        }

        //查询总结果sql
        let countSql = `SELECT count(*) FROM  (${sql}) as c`
        //排序查询
        if(sortLowNum !== null && sortLowNum !== '' && ISAsc !== null && ISAsc !== ''){
          if(ISAsc === 1){
              //升序
              sql = sql + ' ' + `order by ${sortLowNum} asc`
          }else if(ISAsc === 0){
              //降序
              sql = sql + ' ' +`order by ${sortLowNum} desc`
          }
      }
        //分页sql
        if(isPage === 1){
            sql = sql + ' ' +`limit ${newPage},10`
        }
         let result = await db.SySqlConnect(sql,[])
        if(result.length === 0 || !result){
            return res.send({
                code: 2,
                msg: '无查询信息'
            })
        }
        let countAll = await db.SySqlConnect(countSql,[])
        countAll = countAll[0]['count(*)']
        return res.send({
            code: 0,
            msg: `查询成功一共${countAll}条数据`,
            data: {
                countAll,  //总结果数
                length: result.length, //当页结果数
                result,    //用户羊只羊绒信息
            }
        })
    }catch (e) {
        console.log(e);
        return res.send({
            code: 4,
            msg: '服务器错误，请稍后再试'
        })
    }
})


module.exports = router
